﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using ImageTagToolViewModel.Models;

namespace ImageTagToolViewModel.Data
{
    /// <summary>
    /// Class for executing database operations
    /// </summary>
    public static class DatabaseOperationExecutor
    {
        /// <summary>
        /// Stores the connection object
        /// </summary>
        private static SqlCeConnection myConn;

        /// <summary>
        /// Initializes the connection object
        /// </summary>
        static DatabaseOperationExecutor()
        {
            myConn = DatabaseConnector.Instance.Connection;
        }

        /// <summary>
        /// Saves the pictures' info and the list of locations into the database
        /// </summary>
        /// <param name="pictures">list of picture informations</param>
        /// <param name="locations">list of locations</param>
        public static void SaveDataIntoDB(List<Picture> pictures, List<string> locations)
        {
            //Saving the locations
            SqlCeCommand insertComm = myConn.CreateCommand();
            insertComm.CommandText = "insert into Locations (Location) values (@location)";

            SqlCeCommand selectComm = myConn.CreateCommand();
            selectComm.CommandText = "select Id from Locations where Location=@location";

            foreach (string location in locations)
            {
                selectComm.Parameters.Clear();
                selectComm.Parameters.AddWithValue("@location", location);
                var loc = selectComm.ExecuteScalar();

                //If the location doesn't exist in the DB, it will be inserted
                if (loc == null)
                {
                    insertComm.Parameters.Clear();
                    insertComm.Parameters.AddWithValue("@location", location);
                    insertComm.ExecuteNonQuery();
                }                
            }

            //Saving the pictures
            selectComm.CommandText = "select Id from Pictures where FileName=@fileName";
            insertComm.CommandText =
                "insert into Pictures (FileName, Height, Width, HorizontalResolution, VerticalResolution, Filesize, Rating, SceneTags, PersonTags, OtherTags) values (@fileName, @height, @width, @horizontal, @vertical, @filesize, @rating, @sceneTags, @personTags, @otherTags)";

            foreach (Picture picture in pictures)
            {
                selectComm.Parameters.Clear();
                selectComm.Parameters.AddWithValue("@fileName", picture.FileName);
                var pic = selectComm.ExecuteScalar();

                //If the picture doesn't exist in the DB, it will be inserted
                if (pic == null)
                {
                    insertComm.Parameters.Clear();
                    insertComm.Parameters.AddWithValue("@fileName", picture.FileName);
                    insertComm.Parameters.AddWithValue("@height", picture.Height);
                    insertComm.Parameters.AddWithValue("@width", picture.Width);
                    insertComm.Parameters.AddWithValue("@horizontal", picture.HorizontalResolution);
                    insertComm.Parameters.AddWithValue("@vertical", picture.VerticalResolution);
                    insertComm.Parameters.AddWithValue("@filesize", picture.FileSize);
                    insertComm.Parameters.AddWithValue("@rating", picture.Rating);
                    insertComm.Parameters.AddWithValue("@sceneTags", string.Join(",", picture.SceneTags));
                    insertComm.Parameters.AddWithValue("@personTags", string.Join(",", picture.PersonTags));
                    insertComm.Parameters.AddWithValue("@otherTags", string.Join(",", picture.OtherTags));

                    insertComm.ExecuteNonQuery();
                }                
            }
        }

        /// <summary>
        /// Loads the locations from the DB
        /// </summary>
        /// <returns>the list of locations</returns>
        public static List<string> LoadLocationsFromDB()
        {
            List<string> locations = new List<string>();

            SqlCeCommand comm = myConn.CreateCommand();
            comm.CommandText = "select Location from Locations";
            SqlCeDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                locations.Add(reader.GetString(0));
            }
            reader.Close();
            return locations;
        }

        /// <summary>
        /// Loads the pictures' info from the DB
        /// </summary>
        /// <returns>list of picture objects</returns>
        public static List<Picture> LoadPicturesFromDB()
        {
            List<Picture> pictures = new List<Picture>();
            SqlCeCommand comm = myConn.CreateCommand();
            comm.CommandText = "select FileName, Height, Width, HorizontalResolution, VerticalResolution, Filesize, Rating, SceneTags, PersonTags, OtherTags from Pictures";
            SqlCeDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                string fileName = reader.GetString(0);
                int height = reader.GetInt32(1);
                int width = reader.GetInt32(2);
                int horizontal = reader.GetInt32(3);
                int vertical = reader.GetInt32(4);
                int fileSize = reader.GetInt32(5);
                byte rating = reader.GetByte(6);
                string[] sceneTags = reader.GetString(7).Split(',');
                string[] personTags = reader.GetString(8).Split(',');
                string[] otherTags = reader.GetString(9).Split(',');

                Picture picture = new Picture
                {
                    FileName = fileName,
                    Height = height,
                    Width = width,
                    HorizontalResolution = horizontal,
                    VerticalResolution = vertical,
                    FileSize = fileSize,
                    Rating = rating,
                    SceneTags = (sceneTags[0] == string.Empty) ? new List<string>() : sceneTags.ToList(),
                    PersonTags = (personTags[0] == string.Empty) ? new List<string>() : personTags.ToList(),
                    OtherTags = (otherTags[0] == string.Empty) ? new List<string>() : otherTags.ToList(),
                };

                pictures.Add(picture);
            }
            reader.Close();
            return pictures;
        }

    }
}
